# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author: GGA
#        Email:
#     HomePage:
#      Version: 1.0.1
#   LastChange: 2020-12-20
#      History:
# =============================================================================

import os
import pymysql
import pandas
import fire
import logging
from utils.logger_helper import LoggerHelper

APP_NAME = "mysql_to_excel"
logger = LoggerHelper.get_logger(logger_name=APP_NAME)


def get_sql_items():
    sql_files = []
    sql_dir = os.path.join(os.path.dirname(__file__), "sql_files")
    if not os.path.exists(sql_dir):
        os.mkdir(sql_dir)
    tmp_files = os.listdir(sql_dir)
    for file_name in tmp_files:
        if file_name.endswith(".sql"):
            file_title = file_name.replace(".sql", "")
            file_path = os.path.join(sql_dir, file_name)
            file_content = get_file_content(file_path)
            sql_files.append({"file_title": file_title, "file_path": file_path, "file_content": file_content})
    return sql_files


def get_file_content(file_path):
    if not os.path.exists(file_path):
        return ""
    with open(file_path, 'r') as fh:
        file_content = fh.read()
        return file_content


def init_logger():
    LoggerHelper.init_logger(logger_name=APP_NAME, logger_level=logging.DEBUG)
    global logger
    logger = LoggerHelper.get_logger(logger_name=APP_NAME)


def main(mysql_host="127.0.0.1", mysql_port=3306,
         mysql_user="root", mysql_password="",
         database_name="mysql", mysql_charset="utf8mb4"):
    logger.info("开始执行")
    # MySQL数据库连接信息
    mysql_config = {
        'host': mysql_host,
        'port': mysql_port,
        'user': mysql_user,
        'password': mysql_password,
        'database': database_name,
        'charset': mysql_charset
    }
    sql_items = get_sql_items()
    excel_dir = os.path.join(os.path.dirname(__file__), "excel_files")
    if not os.path.exists(excel_dir):
        os.mkdir(excel_dir)
    logger.info("共找到{}个SQL文件".format(len(sql_items)))
    for sql_item in sql_items:
        file_title = sql_item["file_title"]
        logger.info("开始处理文件{}".format(file_title))
        try:
            mysql_script = sql_item["file_content"]
            mysql_conn = pymysql.connect(**mysql_config)
            df = pandas.read_sql(mysql_script, mysql_conn)
            mysql_conn.close()  # 关闭数据库连接
            excel_file_name = file_title + ".xlsx"
            excel_file_path = os.path.join(excel_dir, excel_file_name)
            df.to_excel(excel_file_path, index=False)
            logger.info("完成处理文件{}".format(file_title))
        except Exception as ex:
            logger.info("处理文件{}失败\n异常为:{}".format(file_title, str(ex)))
            logger.warning(str(ex))
        logger.info("所有文件处理完成")


if __name__ == '__main__':
    init_logger()
    fire.Fire(main)
